﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;

namespace ARR.Services.Domain.Reports
{
    public static class ExcelHelper
    {
        public static void SetBorders(ExcelRange cells, ExcelBorderStyle borderStyle)
        {
            cells.Style.Border.Left.Style = borderStyle;
            cells.Style.Border.Right.Style = borderStyle;
            cells.Style.Border.Top.Style = borderStyle;
            cells.Style.Border.Bottom.Style = borderStyle;
        }

        public static void SetBorders(ExcelRange cells, ExcelBorderStyle borderStyle, Color borderColor)
        {

            SetBorders(cells, borderStyle);

            cells.Style.Border.Left.Color.SetColor(borderColor);
            cells.Style.Border.Right.Color.SetColor(borderColor);
            cells.Style.Border.Top.Color.SetColor(borderColor);
            cells.Style.Border.Bottom.Color.SetColor(borderColor);
        }

        public static void SetStyles(ExcelRange cells, ExcelBorderStyle borderStyle, Color fillColor)
        {
            SetBorders(cells, borderStyle);

            cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
            cells.Style.Fill.BackgroundColor.SetColor(fillColor);
        }

        public static void SetStyles(ExcelRange cells, ExcelBorderStyle borderStyle, Color borderColor, Color fillColor)
        {
            SetBorders(cells, borderStyle, borderColor);

            cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
            cells.Style.Fill.BackgroundColor.SetColor(fillColor);
        }

        public static ExcelRange ChangeStyles(this ExcelRange cells, ExcelBorderStyle borderStyle, Color borderColor, Color fillColor)
        {
            SetStyles(cells, borderStyle, borderColor, fillColor);

            return cells;
        }

        public static int GetColumnPosition(string columnName)
        {
            int colPosition = 0;

            foreach (char letter in columnName.ToUpper().ToCharArray())
            {
                colPosition += LetterToNumber(letter);
            }

            return colPosition;
        }

        public static int LetterToNumber(char oneLetter)
        {
            switch (oneLetter)
            {
                case 'A': return 1;
                case 'B': return 2;
                case 'C': return 3;
                case 'D': return 4;
                case 'E': return 5;
                case 'F': return 6;
                case 'G': return 7;
                case 'H': return 8;
                case 'I': return 9;
                case 'J': return 10;
                case 'K': return 11;
                case 'L': return 12;
                case 'M': return 13;
                case 'N': return 14;
                case 'O': return 15;
                case 'P': return 16;
                case 'Q': return 17;
                case 'R': return 18;
                case 'S': return 19;
                case 'T': return 20;
                case 'U': return 21;
                case 'V': return 22;
                case 'W': return 23;
                case 'X': return 24;
                case 'Y': return 25;
                case 'Z': return 26;
            }

            return 0;
        }

        public static ExcelAddressNumbers SplitAddress(string address)
        {
            ExcelAddressNumbers AddressNumbers = new ExcelAddressNumbers();
            string row = string.Empty;

            string[] range = address.ToUpper().Split(':');

            //RANGE FROM
            string rangeFrom = range[0];
            int exp = -1;
            foreach(char ch in rangeFrom.ToCharArray())
            {
                if(LetterToNumber(ch) != 0)
                {
                    exp++;
                }
            }

            foreach (char ch in rangeFrom.ToCharArray())
            {
                if (LetterToNumber(ch) != 0)
                {                    
                    AddressNumbers.FromColumn += Convert.ToInt32(LetterToNumber(ch) * (Math.Pow(26, exp--)));
                }
                else
                {
                    row += ch;
                }
            }
            
            int.TryParse(row, out AddressNumbers.FromRow);

            //RANGE TO
            row = string.Empty;
            if (range.Count() > 1)
            {
                string rangeTo = range[1];
                foreach (char ch in rangeTo.ToCharArray())
                {
                    if (LetterToNumber(ch) != 0)
                    {
                        AddressNumbers.ToColumn += LetterToNumber(ch);
                    }
                    else
                    {
                        row += ch;
                    }
                }
                int.TryParse(row, out AddressNumbers.ToRow);
            }

            return AddressNumbers;
        }
    }

    public struct ExcelAddressNumbers
    {
        public int FromColumn;

        public int FromRow;

        public int ToColumn;

        public int ToRow;
    }
}
